Constructing the COVID dashboard elements¶

In [1]:
import numpy as np
import pandas as pd
import requests
import json
import plotly.express as px
import dash
from datetime import timedelta

Pull latest data from VDH¶

In [2]:
endpoint = 'https://data.virginia.gov/resource/bre9-aqqr.json'
mypars = {'$limit': 150000}
headers = {'User-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:99.0) Gecko/20100101 Firefox/99.0'}
r = requests.get(endpoint, params=mypars, headers=headers)
r
Out[2]:
<Response [200]>
In [3]:
cases = pd.json_normalize(json.loads(r.text))
cases
Out[3]:
report_date fips locality vdh_health_district total_cases hospitalizations deaths
0 2022-07-18T00:00:00.000 51001 Accomack Eastern Shore 7628 417 108
1 2022-07-18T00:00:00.000 51003 Albemarle Blue Ridge 21101 489 173
2 2022-07-18T00:00:00.000 51005 Alleghany Alleghany 3667 97 100
3 2022-07-18T00:00:00.000 51007 Amelia Piedmont 2900 169 59
4 2022-07-18T00:00:00.000 51009 Amherst Central Virginia 7998 349 93
... ... ... ... ... ... ... ...
113577 2020-03-17T00:00:00.000 51800 Suffolk Western Tidewater 0 0 0
113578 2020-03-17T00:00:00.000 51810 Virginia Beach Virginia Beach 4 0 0
113579 2020-03-17T00:00:00.000 51820 Waynesboro Central Shenandoah 0 0 0
113580 2020-03-17T00:00:00.000 51830 Williamsburg Peninsula 1 0 0
113581 2020-03-17T00:00:00.000 51840 Winchester Lord Fairfax 0 0 0

113582 rows × 7 columns

Overall data cleaning¶

In [4]:
cases['report_date'] = pd.to_datetime(cases['report_date'])
cases['total_cases'] = cases['total_cases'].astype('int')
cases['hospitalizations'] = cases['hospitalizations'].astype('int')
cases['deaths'] = cases['deaths'].astype('int')
cases_today = cases.loc[cases['report_date'] == max(cases['report_date'])]

Creation of per-capita dataset¶

In [5]:
url = "https://demographics.coopercenter.org/sites/demographics/files/media/files/2020-07/Census_2019_RaceEstimates_forVA_0.xls"
pop = pd.read_excel(url, skiprows=4)
pop = pop.loc[~pop['FIPS'].isna()]
pop['FIPS'] = pop['FIPS'] + 51000
pop['FIPS'] = pop['FIPS'].astype('int').astype('str')
pop = pop[['FIPS', 'Total Population']]
pop
Out[5]:
FIPS Total Population
3 51001 32316.0
4 51003 109330.0
5 51005 14860.0
6 51007 13145.0
7 51009 31605.0
... ... ...
131 51800 92108.0
132 51810 449974.0
133 51820 22630.0
134 51830 14954.0
135 51840 28078.0

133 rows × 2 columns

In [6]:
cases_pop = pd.merge(cases_today, pop, 
                    left_on = ['fips'],
                    right_on = ['FIPS'],
                    how = 'inner')
In [7]:
cases_pop['Cases per 1000 people'] = round(1000*cases_pop['total_cases']/cases_pop['Total Population'],1)
cases_pop['Hospitalizations per 1000 people'] = round(1000*cases_pop['hospitalizations']/cases_pop['Total Population'],1)
cases_pop['Deaths per 1000 people'] = round(1000*cases_pop['deaths']/cases_pop['Total Population'],1)

Choropleth map and table¶

In [8]:
r = requests.get('https://raw.githubusercontent.com/plotly/datasets/master/geojson-counties-fips.json')
counties = json.loads(r.text)
In [9]:
fig = px.choropleth(cases_pop, geojson=counties, locations='fips', 
                    color='Cases per 1000 people',
                    color_continuous_scale="Viridis",
                    scope="usa",
                    labels={'total_cases':'Cases per 1000 people'},
                    hover_name = 'locality'
                   )
fig.update_geos(fitbounds="locations")
fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})
fig.show()
In [10]:
cases_table = cases_pop[['locality', 'Cases per 1000 people',
                         'Hospitalizations per 1000 people',
                         'Deaths per 1000 people']]
dash.dash_table.DataTable(
        id='datatable-interactivity',
        columns=[
            {"name": i, "id": i, "deletable": True, "selectable": True} for i in cases_table.columns
        ],
        data=cases_table,
        editable=True,
        filter_action="native",
        sort_action="native",
        sort_mode="multi",
        column_selectable="single",
        row_selectable="multi",
        row_deletable=True,
        selected_columns=[],
        selected_rows=[],
        page_action="native",
        page_current= 0,
        page_size= 10)
Out[10]:
DataTable(data=           locality  Cases per 1000 people  Hospitalizations per 1000 people  \
0          Accomack                  236.0                              12.9   
1         Albemarle                  193.0                               4.5   
2         Alleghany                  246.8                               6.5   
3            Amelia                  220.6                              12.9   
4           Amherst                  253.1                              11.0   
..              ...                    ...                               ...   
128         Suffolk                  227.4                               9.0   
129  Virginia Beach                  223.0                               7.6   
130      Waynesboro                  284.9                               7.6   
131    Williamsburg                  131.7                               4.1   
132      Winchester                  245.1                               7.2   

     Deaths per 1000 people  
0                       3.3  
1                       1.6  
2                       6.7  
3                       4.5  
4                       2.9  
..                      ...  
128                     3.4  
129                     1.9  
130                     3.0  
131                     1.5  
132                     2.9  

[133 rows x 4 columns], columns=[{'name': 'locality', 'id': 'locality', 'deletable': True, 'selectable': True}, {'name': 'Cases per 1000 people', 'id': 'Cases per 1000 people', 'deletable': True, 'selectable': True}, {'name': 'Hospitalizations per 1000 people', 'id': 'Hospitalizations per 1000 people', 'deletable': True, 'selectable': True}, {'name': 'Deaths per 1000 people', 'id': 'Deaths per 1000 people', 'deletable': True, 'selectable': True}], editable=True, column_selectable='single', row_selectable='multi', row_deletable=True, selected_rows=[], selected_columns=[], page_action='native', page_current=0, page_size=10, filter_action='native', sort_action='native', sort_mode='multi', id='datatable-interactivity')

Create the trend dataset¶

In [11]:
cases_trend = cases[['report_date', 'fips', 'locality', 'total_cases', 'hospitalizations', 'deaths']]
cases_trend = pd.melt(cases_trend, id_vars = ['report_date', 'fips', 'locality'],
                     value_vars = ['total_cases', 'hospitalizations', 'deaths'])
cases_trend['date14'] = cases_trend['report_date'] + timedelta(14)
cases_trend['date28'] = cases_trend['report_date'] + timedelta(28)

cases_trend = pd.merge(cases_trend, cases_trend,
                      right_on = ['report_date', 'fips', 'locality', 'variable'],
                      left_on = ['date14', 'fips', 'locality', 'variable'])

cases_trend = cases_trend.drop(['report_date_x','date14_x','date28_x'], axis=1)
cases_trend = cases_trend.rename({'report_date_y':'report_date',
                                 'date14_y':'date14',
                                 'date28_y':'date28',
                                 'value_y':'value',
                                 'value_x':'value14'}, axis=1)

cases_trend = pd.merge(cases_trend, cases_trend,
                      right_on = ['report_date', 'fips', 'locality', 'variable'],
                      left_on = ['date28', 'fips', 'locality', 'variable'])

cases_trend = cases_trend.drop(['report_date_x','date14_y','date28_y', 'value14_x', 'date14_x', 'date28_x'], axis=1)
cases_trend = cases_trend.rename({'report_date_y':'report_date',
                                 'value_y':'value',
                                 'value14_y':'value14',
                                 'value_x':'value28'}, axis=1)
#cases_trend = cases_trend.loc[cases_trend['report_date'] == max(cases_trend['report_date'])]
cases_trend['Most recent 14 days'] = cases_trend['value'] - cases_trend['value14']
cases_trend['Previous 14 days'] = cases_trend['value14'] - cases_trend['value28']
cases_trend['Trend'] = round(100*(cases_trend['Most recent 14 days'] - cases_trend['Previous 14 days']) / cases_trend['Previous 14 days'], 1)
cases_trend['Trend'] = cases_trend['Trend'].astype('str') + "%"
cases_trend
Out[11]:
fips locality variable value28 value14 report_date value Most recent 14 days Previous 14 days Trend
0 51001 Accomack total_cases 7325 7456 2022-07-18 7628 172 131 31.3%
1 51003 Albemarle total_cases 20284 20719 2022-07-18 21101 382 435 -12.2%
2 51005 Alleghany total_cases 3480 3552 2022-07-18 3667 115 72 59.7%
3 51007 Amelia total_cases 2802 2850 2022-07-18 2900 50 48 4.2%
4 51009 Amherst total_cases 7675 7807 2022-07-18 7998 191 132 44.7%
... ... ... ... ... ... ... ... ... ... ...
323983 51800 Suffolk deaths 0 1 2020-04-28 8 7 1 600.0%
323984 51810 Virginia Beach deaths 2 5 2020-04-28 12 7 3 133.3%
323985 51820 Waynesboro deaths 0 0 2020-04-28 0 0 0 nan%
323986 51830 Williamsburg deaths 1 1 2020-04-28 1 0 0 nan%
323987 51840 Winchester deaths 0 0 2020-04-28 0 0 0 nan%

323988 rows × 10 columns

Line plot and table for one locality¶

In [12]:
loc = 'Alleghany'
today = max(cases_trend['report_date']).strftime('%B %d, %Y')
title = f"COVID Statistics for {loc} through {today}"
cases_loc = cases_trend.query(f"locality == '{loc}'")
fig = px.line(cases_loc, x='report_date', y='value', color='variable', facet_row='variable', 
                 hover_data=['Most recent 14 days', 'Previous 14 days', 'Trend'],
                 labels={'report_date':'Date', 
                      'value':'Total'},
                 title = title,
                 width=1000, height=800)
fig.update(layout=dict(title=dict(x=0.5)))
fig.update_layout(showlegend=False)
fig.for_each_annotation(lambda a: a.update(text=a.text.replace("variable=", "")))
fig.update_yaxes(matches=None)
fig.show()
In [13]:
tab = cases_loc.loc[cases_loc['report_date'] == max(cases_loc['report_date'])][['variable', 
                                                                          'Most recent 14 days',
                                                                         'Previous 14 days',
                                                                         'Trend']]
In [14]:
dash.dash_table.DataTable(
        id='datatable-interactivity',
        columns=[
            {"name": i, "id": i, "deletable": True, "selectable": True} for i in tab.columns
        ],
        data=tab,
        editable=True,
        filter_action="native",
        sort_action="native",
        sort_mode="multi",
        column_selectable="single",
        row_selectable="multi",
        row_deletable=True,
        selected_columns=[],
        selected_rows=[],
        page_action="native",
        page_current= 0,
        page_size= 10)
Out[14]:
DataTable(data=                variable  Most recent 14 days  Previous 14 days   Trend
2            total_cases                  115                72   59.7%
107998  hospitalizations                    2                 1  100.0%
215994            deaths                    3                 0    inf%, columns=[{'name': 'variable', 'id': 'variable', 'deletable': True, 'selectable': True}, {'name': 'Most recent 14 days', 'id': 'Most recent 14 days', 'deletable': True, 'selectable': True}, {'name': 'Previous 14 days', 'id': 'Previous 14 days', 'deletable': True, 'selectable': True}, {'name': 'Trend', 'id': 'Trend', 'deletable': True, 'selectable': True}], editable=True, column_selectable='single', row_selectable='multi', row_deletable=True, selected_rows=[], selected_columns=[], page_action='native', page_current=0, page_size=10, filter_action='native', sort_action='native', sort_mode='multi', id='datatable-interactivity')
In [ ]: